package com.xiaolin.admin.utils.excel;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
import com.xiaolin.admin.utils.excel.bean.ExcelCellStyle;
import com.xiaolin.admin.utils.excel.bean.ExcelFieldType;
import com.xiaolin.admin.utils.excel.bean.ExportFieldProperties;
import com.xiaolin.admin.utils.excel.bean.MergeModel;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import sun.misc.BASE64Decoder;

import javax.imageio.ImageIO;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.nio.charset.StandardCharsets;
import java.security.GeneralSecurityException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

/**
 * @Auther: xiong
 * @Date: 2020/8/18 0018 16:18
 * @Description:excel表格导出工具类
 */
public class ExcelExportUtil {
    //标题
    private String title;
    // 页签名
    private String sheetName = "Sheet";
    //表头
    private String[][] headList;
    //表尾
    private String[][] footerList;
    //各个列的元素key值
    private String[] headKey;
    //需要填充的数据信息
    private List<Map<String, Object>> data;
    // 需要指定的数据格式
    private Map<String, ExportFieldProperties> fieldTypes = new HashMap<>();
    // 制定图片的位置（echarts）
    private Map<String, Integer> imgLocation = new HashMap<>();
    //需要插入的图片（echarts Base64编码）
    public List<String> imgUrls;
    // 是否需要汇总行
    private boolean needSumRow;
    //是否需要插入图片
    private boolean needImg = false;
    // 合计行名称
    private String sumRowName = "合计";
    // 要忽略汇总的字段
    private String[] ignoreSumFields;
    // 表尾偏移量
    private int footerOffset;
    //列宽
    private int columnWidth;
    //是否需要序号（注：该字段是自动生成序号，如果设置自动生成，外部不能传序号进来）
    private boolean needSerialNumber = true;
    // 每个sheet的行最大数
    private int sheetMaxRow = 100000;
    // 是否需要加密
    private boolean encryption = false;
    // 加密密码
    private String password = "123321";
    // 固定行
    private int freezeRows;
    // 固定列
    private int freezeCols;
    // 是否为number类型填充0值
    private boolean fillZeroForNumber = true;
    // 标题样式
    private ExcelCellStyle titleStyle;
    // 表头样式
    private ExcelCellStyle headerStyle;
    // 数据样式
    private ExcelCellStyle dataStyle;
    // 表尾样式
    private ExcelCellStyle footerStyle;
    // 数字格式的样式
    private Map<String, XSSFCellStyle> numberCellStyle = new HashMap<>();
    //是否合并
    private boolean isMerge = false;
    //合并单元格
    private List<Integer> mergeIndex;
    //是否树形结构
    private boolean isTree = false;

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public String[][] getHeadList() {
        return headList;
    }

    public void setHeadList(String[][] headList) {
        this.headList = headList;
    }

    public String[][] getFooterList() {
        return footerList;
    }

    public void setFooterList(String[][] footerList) {
        this.footerList = footerList;
    }

    public String[] getHeadKey() {
        return headKey;
    }

    public void setHeadKey(String[] headKey) {
        this.headKey = headKey;
        //初始化图片位置
        setImgLocation();
    }

    public List<Map<String, Object>> getData() {
        return data;
    }

    public void setData(List<Map<String, Object>> data) {
        this.data = data;
    }

    public Map<String, ExportFieldProperties> getFieldTypes() {
        return fieldTypes;
    }

    public void setFieldTypes(Map<String, ExportFieldProperties> fieldTypes) {
        this.fieldTypes = fieldTypes;
    }

    public boolean isNeedSumRow() {
        return needSumRow;
    }

    public void setNeedSumRow(boolean needSumRow) {
        this.needSumRow = needSumRow;
    }

    public String getSumRowName() {
        return sumRowName;
    }

    public void setSumRowName(String sumRowName) {
        this.sumRowName = sumRowName;
    }

    public String[] getIgnoreSumFields() {
        return ignoreSumFields;
    }

    public void setIgnoreSumFields(String[] ignoreSumFields) {
        this.ignoreSumFields = ignoreSumFields;
    }

    public int getFooterOffset() {
        return footerOffset;
    }

    public void setFooterOffset(int footerOffset) {
        this.footerOffset = footerOffset;
    }

    public int getColumnWidth() {
        return columnWidth;
    }

    public void setColumnWidth(int columnWidth) {
        this.columnWidth = columnWidth;
    }

    public boolean isNeedSerialNumber() {
        return needSerialNumber;
    }

    public void setNeedSerialNumber(boolean needSerialNumber) {
        this.needSerialNumber = needSerialNumber;
    }

    public int getSheetMaxRow() {
        return sheetMaxRow;
    }

    public void setSheetMaxRow(int sheetMaxRow) {
        this.sheetMaxRow = sheetMaxRow;
    }

    public boolean isEncryption() {
        return encryption;
    }

    public void setEncryption(boolean encryption) {
        this.encryption = encryption;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getFreezeRows() {
        return freezeRows;
    }

    public void setFreezeRows(int freezeRows) {
        this.freezeRows = freezeRows;
    }

    public int getFreezeCols() {
        return freezeCols;
    }

    public void setFreezeCols(int freezeCols) {
        this.freezeCols = freezeCols;
    }

    public boolean isFillZeroForNumber() {
        return fillZeroForNumber;
    }

    public void setFillZeroForNumber(boolean fillZeroForNumber) {
        this.fillZeroForNumber = fillZeroForNumber;
    }

    public ExcelCellStyle getTitleStyle() {
        return titleStyle;
    }

    public ExcelCellStyle getHeaderStyle() {
        return headerStyle;
    }

    public ExcelCellStyle getDataStyle() {
        return dataStyle;
    }

    public ExcelCellStyle getFooterStyle() {
        return footerStyle;
    }

    public List<String> getImgUrls() {
        return imgUrls;
    }

    public void setImgUrls(List<String> imgUrls) {
        this.imgUrls = imgUrls;
    }

    public boolean isNeedImg() {
        return needImg;
    }

    public void setNeedImg(boolean needImg) {
        this.needImg = needImg;
    }

    public Map<String, Integer> getImgLocation() {
        return imgLocation;
    }

    /**
     * 初始化图片位置
     */
    public void setImgLocation() {
        Map<String, Integer> map = new HashMap<>();
        map.put("startColumn", headKey.length + 2); //嵌入对象左上角所在单元格的列号
        map.put("columnWidth", 15);  //嵌入对象左上角所在单元格的行号
        map.put("startRow", 0);  //嵌入对象右下角所在单元格的列号
        map.put("rowHeight", 35);  //嵌入对象右下角所在单元格的行号
        this.imgLocation = map;
    }

    public void setImgLocation(Map<String, Integer> imgLocation) {
        this.imgLocation = imgLocation;
    }

    public boolean isMerge() {
        return isMerge;
    }

    public void setIsMerge(boolean isMerge) {
        this.isMerge = isMerge;
    }

    public List<Integer> getMergeIndex() {
        return mergeIndex;
    }

    public void setMergeIndex(List<Integer> mergeIndex) {
        this.mergeIndex = mergeIndex;
    }

    public boolean isTree() {
        return isTree;
    }

    public void setIsTree(boolean isTree) {
        this.isTree = isTree;
    }

    public ExcelExportUtil() {
        titleStyle = ExcelCellStyle.builder()
                .fontSize(16).color(HSSFColor.HSSFColorPredefined.BLACK.getIndex()).backgroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex()).bold(true)
                .build();
        headerStyle = ExcelCellStyle.builder()
                .fontSize(14).color(HSSFColor.HSSFColorPredefined.BLACK.getIndex()).backgroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex()).bold(true)
                .build();
        dataStyle = ExcelCellStyle.builder().
                fontSize(12).color(HSSFColor.HSSFColorPredefined.BLACK.getIndex()).backgroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex()).bold(false)
                .build();
        footerStyle = ExcelCellStyle.builder().
                fontSize(12).color(HSSFColor.HSSFColorPredefined.BLACK.getIndex()).backgroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex()).bold(false)
                .alignment(HorizontalAlignment.LEFT)
                .build();
    }

    /**
     * 开始导出数据信息
     */
    public void exportExport(HttpServletResponse response) throws IOException {
        XSSFWorkbook wb = getExcelWorkBook();

        this.exportExport(wb, response);
    }

    public void exportExport(XSSFWorkbook wb, HttpServletResponse response) throws IOException {
        //导出数据
        try {
            //防止中文乱码
            String headerValue = "attachment";
            response.setContentType("octets/stream");
            response.setContentType("APPLICATION/OCTET-STREAM");
            response.setHeader("Content-Disposition", headerValue);
            if (encryption) {
                // 全局开启加密且自定义开启加密时进行加密，使用基于内存进行加密
                excelEncrypt(wb, response, password);
            } else {
                // Excel不需要加密
                OutputStream os = response.getOutputStream();
                wb.write(os);
                os.flush();
                os.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw new IOException("导出Excel出现严重异常，异常信息：" + ex.getMessage());
        }
    }


    /**
     * 生成Excel对象
     *
     * @return
     */
    public XSSFWorkbook getExcelWorkBook() throws IOException {
        //检查参数配置信息
        checkConfig();
        // 填充序号字段
        fillHeaderAndKey();
        //创建工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        // 页签数量
        int sheetNum = data.size() % sheetMaxRow == 0 ? data.size() / sheetMaxRow : (data.size() / sheetMaxRow) + 1;
        if (sheetNum == 0) {
            // 没有数据的情况，构建表头
            String sheetName = this.sheetName;
            //创建工作表
            XSSFSheet wbSheet = wb.createSheet(sheetName);
            //设置默认行宽
            if (columnWidth != 0) {
                wbSheet.setDefaultColumnWidth(columnWidth);
            }
            // 设置冻结窗口
            if (freezeRows > 0 && freezeCols > 0) {
                wbSheet.createFreezePane(freezeCols, freezeRows);
            } else if (freezeRows > 0) {
                wbSheet.createFreezePane(0, freezeRows);
            } else if (freezeCols > 0) {
                wbSheet.createFreezePane(freezeCols, 0);
            }
            // 表头起始行
            int headerIndex = 0;
            if (StringUtils.isNotBlank(title)) {
                // 标题样式（加粗，居中）
                XSSFCellStyle titleCellStyle = buildCellStyle(wb, titleStyle);
                // 构建标题
                createTitle(wbSheet, titleCellStyle);
                // 表头起始行设置为 1
                headerIndex = 1;
            }
            // 表头样式（加粗，居中）
            XSSFCellStyle headerCellStyle = buildCellStyle(wb, headerStyle);
            // 构建表头
            createHeader(wbSheet, headerCellStyle, headList, headerIndex);
        }
        for (int s = 0; s < sheetNum; s++) {
            String sheetName = this.sheetName + (s == 0 ? "" : s + 1);
            //创建工作表
            XSSFSheet wbSheet = wb.createSheet(sheetName);
            //设置默认行宽
            if (columnWidth != 0) {
                wbSheet.setDefaultColumnWidth(columnWidth);
            }

            // 设置冻结窗口
            if (freezeRows > 0 && freezeCols > 0) {
                wbSheet.createFreezePane(freezeCols, freezeRows);
            } else if (freezeRows > 0) {
                wbSheet.createFreezePane(0, freezeRows);
            } else if (freezeCols > 0) {
                wbSheet.createFreezePane(freezeCols, 0);
            }

            // 表头起始行
            int headerIndex = 0;

            int headerRow = headList.length;

            if (StringUtils.isNotBlank(title)) {
                // 标题样式（加粗，居中）
                XSSFCellStyle titleCellStyle = buildCellStyle(wb, titleStyle);
                // 构建标题
                createTitle(wbSheet, titleCellStyle);
                // 表头起始行设置为 1
                headerIndex = 1;
            }

            // 表头样式（加粗，居中）
            XSSFCellStyle headerCellStyle = buildCellStyle(wb, headerStyle);

            // 构建表头
            createHeader(wbSheet, headerCellStyle, headList, headerIndex);

            // 数据样式（居中）
            XSSFCellStyle dataCellStyle = buildCellStyle(wb, dataStyle);

            // 每个sheet的起始数据
            int startData = s * sheetMaxRow;
            // 每个sheet的结束数据
            int endData = (s < data.size() / sheetMaxRow) ? (s + 1) * sheetMaxRow - 1 : data.size() - 1;
            // 每个sheet的数据
            List<Map<String, Object>> sheetData = copyList(data, startData, endData);

            if (isTree) {
                dataCellStyle = wb.createCellStyle();
                dataCellStyle.setAlignment(dataStyle.getAlignment());//水平居中
                dataCellStyle.setVerticalAlignment(dataStyle.getVerticalAlignment());//垂直居中
                XSSFFont fontStyle = wb.createFont();
                fontStyle.setColor((short) dataStyle.getColor());
                fontStyle.setFontName(dataStyle.getFontName());
                fontStyle.setBold(dataStyle.isBold());
                fontStyle.setFontHeightInPoints((short) dataStyle.getFontSize());  //设置标题字体大小
                dataCellStyle.setFont(fontStyle);
                createMargeDateRow(wbSheet, dataCellStyle, sheetData, headerIndex, headerRow);
            } else {
                // 构建数据行
                createDataRow(wb, wbSheet, dataCellStyle, sheetData, headerIndex, headerRow);
            }
            if (needImg) {
                // 构建图像图表
                createImg(wb, wbSheet);
            }

            if (footerList != null && footerList.length > 0) {
                // 表尾样式
                XSSFCellStyle footerCellStyle = buildCellStyle(wb, footerStyle);
                // 表尾起始行
                int footerIndex = headerIndex + headList.length + sheetData.size();
                // 构建表尾
                createFooter(wbSheet, footerCellStyle, footerList, footerIndex);
            }
        }
        return wb;
    }


    /**
     * 在内存中对Excel进行加密
     *
     * @param wb
     * @param response
     * @param password
     * @throws IOException
     * @throws InvalidFormatException
     * @throws GeneralSecurityException
     */
    public void excelEncrypt(XSSFWorkbook wb, HttpServletResponse response, String password) throws IOException, InvalidFormatException, GeneralSecurityException {
        // Excel 转成输出流
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        wb.write(bos);
        // 设置加密密码
        EncryptionInfo encryptionInfo = new EncryptionInfo(EncryptionMode.standard);
        Encryptor encryptor = encryptionInfo.getEncryptor();
        encryptor.confirmPassword(password);
        // 对输入流进行加密处理
        OPCPackage opcPackage = OPCPackage.open(new ByteArrayInputStream(bos.toByteArray()));
        POIFSFileSystem poifsFileSystem = new POIFSFileSystem();
        OutputStream eos = encryptor.getDataStream(poifsFileSystem);
        opcPackage.save(eos);
        opcPackage.close();
        // 输出加密后的流文件
        ServletOutputStream os = response.getOutputStream();
        poifsFileSystem.writeFilesystem(os);
        os.flush();
        os.close();
    }

    /**
     * 借助磁盘对文件进行加密
     *
     * @param wb
     * @param filePath
     * @param password
     * @throws IOException
     * @throws InvalidFormatException
     * @throws GeneralSecurityException
     */
    public void excelEncrypt(XSSFWorkbook wb, HttpServletResponse response, String filePath, String password) throws IOException, InvalidFormatException, GeneralSecurityException {
        // Excel输出到磁盘
        wb.write(new FileOutputStream(filePath));
        // 设置加密密码
        EncryptionInfo encryptionInfo = new EncryptionInfo(EncryptionMode.standard);
        Encryptor encryptor = encryptionInfo.getEncryptor();
        encryptor.confirmPassword(password);
        // 磁盘文件读取成流并进行加密处理
        OPCPackage opc = OPCPackage.open(filePath, PackageAccess.READ_WRITE);
        POIFSFileSystem fileSystem = new POIFSFileSystem();
        OutputStream eos = encryptor.getDataStream(fileSystem);
        opc.save(eos);
        opc.close();
        // 输出加密后的流文件
        ServletOutputStream ros = response.getOutputStream();
        fileSystem.writeFilesystem(ros);
        ros.flush();
        ros.close();
    }

    /**
     * 构建表尾
     *
     * @param wbSheet
     * @param cellStyle
     * @param index
     */
    protected void createFooter(XSSFSheet wbSheet, XSSFCellStyle cellStyle, String[][] hfDataList, int index) {
        for (int i = 0; i < hfDataList.length; i++) {
            // 表尾字段
            String[] footer = hfDataList[i];
            // 表尾索引
            int rowIndex = i + index + footerOffset;
            // 表尾行
            XSSFRow mergeRow = wbSheet.createRow(rowIndex);
            int colIndex = 0;
            for (String con : footer) {
                // 分割标题跟要合并的列数
                String[] titleArr = con.split("==");
                XSSFCell cell = mergeRow.createCell(colIndex);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(titleArr[0]);
                if (titleArr.length == 1) {
                    // 不传列数则默认不合并
                    colIndex++;
                } else {
                    // 合并列数
                    int mergeValue = Integer.parseInt(titleArr[1]);
                    // 要合并的列索引
                    int mergeIndex = colIndex + mergeValue - 1;
                    for (int ci = colIndex + 1; ci <= mergeIndex; ci++) {
                        // 创建被合并的单元格
                        mergeRow.createCell(ci).setCellStyle(cellStyle);
                    }
                    // 合并单元格
                    wbSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, mergeIndex));
                    colIndex += mergeValue;
                }
            }
        }
    }

    /**
     * 构建数据行
     *
     * @param wb
     * @param wbSheet
     * @param dataCellStyle
     * @param sheetData
     * @param headerIndex
     * @param headerRow
     */
    protected void createDataRow(XSSFWorkbook wb, XSSFSheet wbSheet, XSSFCellStyle dataCellStyle, List<Map<String, Object>> sheetData, int headerIndex, int headerRow) {
        // 行迭代
        int dataIterator = headerIndex + headerRow;
        // 汇总行
        Map<String, Object> sumRow = new HashMap<>();
        // 是否已填充汇总行
        boolean fillSumRow = false;
        for (int i = 0; i < sheetData.size(); i++) {
            XSSFRow roww = wbSheet.createRow(dataIterator);
            Map<String, Object> map = sheetData.get(i);
            //加载列数据
            for (int j = 0; j < headKey.length; j++) {
                String key = headKey[j];
                XSSFCell cell = roww.createCell(j);
                cell.setCellStyle(dataCellStyle);
                if ("index".equals(key) && needSerialNumber) {
                    if (!fillSumRow) {
                        int valueObject = i + 1;
                        // 序号直接转成int类型
                        cell.setCellValue(valueObject);
                    } else {
                        cell.setCellValue(sumRowName);
                    }
                } else {
                    Object valueObject = map.get(key);
                    ExportFieldProperties fieldProperties = fieldTypes.get(key);
                    if (Objects.isNull(fieldProperties)) {
                        // 没有设置属性类型，则直接赋值成字符串
                        cell.setCellValue(!isEmpty(valueObject) ? valueObject.toString() : "");
                    } else {
                        String fieldType = fieldProperties.getType().toLowerCase();
                        int point = fieldProperties.getPoint();
                        String format = fieldProperties.getFormat();
                        switch (fieldType) {
                            case ExcelFieldType.NUMBER:
                                // Integer或是Long或是Float或是Double或是BigDecimal类型
                                if (fillSumRow && isEmpty(valueObject)) {
                                    cell.setCellValue("");
                                    break;
                                }
                                BigDecimal numberValue = BigDecimal.ZERO;
                                String valueStr = !isEmpty(valueObject) ? valueObject.toString().trim() : "";
                                if (StringUtils.isNotBlank(valueStr)) {
                                    numberValue = new BigDecimal(valueStr).setScale(point, RoundingMode.HALF_UP);
                                    cell.setCellValue(numberValue.doubleValue());
                                } else if (fillZeroForNumber) {
                                    cell.setCellValue(numberValue.doubleValue());
                                }
                                XSSFCellStyle numberStyle = numberCellStyle.get(key);
                                if (Objects.isNull(numberStyle)) {
                                    numberStyle = (XSSFCellStyle) dataCellStyle.clone();
                                    XSSFDataFormat dataFormat = wb.createDataFormat();
                                    numberStyle.setDataFormat(dataFormat.getFormat(generateDateFormat(point)));
                                    numberCellStyle.put(key, numberStyle);
                                }
                                cell.setCellStyle(numberStyle);
                                if (needSumRow && (Objects.isNull(ignoreSumFields) || !Arrays.asList(ignoreSumFields).contains(key))) {
                                    // 计算合计行数据
                                    Object sumValue = sumRow.get(key);
                                    if (Objects.isNull(sumValue)) {
                                        sumRow.put(key, numberValue);
                                    } else {
                                        sumRow.put(key, ((BigDecimal) sumValue).add(numberValue));
                                    }
                                }
                                break;
                            case ExcelFieldType.DATE:
                                // 日期类型
                                if (!isEmpty(valueObject)) {
                                    Date dateValue;
                                    if (valueObject instanceof Long) {
                                        dateValue = new Date((Long) valueObject);
                                        String dateStr = StringUtils.isNotBlank(format) ? new SimpleDateFormat(format).format(dateValue) : new SimpleDateFormat("yyyy-MM-dd").format(dateValue);
                                        cell.setCellValue(dateStr);
                                    } else if (valueObject instanceof String) {
                                        cell.setCellValue(valueObject.toString());
                                    } else {
                                        dateValue = (Date) valueObject;
                                        String dateStr = StringUtils.isNotBlank(format) ? new SimpleDateFormat(format).format(dateValue) : new SimpleDateFormat("yyyy-MM-dd").format(dateValue);
                                        cell.setCellValue(dateStr);
                                    }
                                } else {
                                    cell.setCellValue("");
                                }
                                break;
                            default:
                                // 如果是字符串或是没有指定类型，直接转成字符串
                                cell.setCellValue(!isEmpty(valueObject) ? valueObject.toString() : "");
                                break;
                        }
                    }
                }
            }
            if (i == sheetData.size() - 1 && !fillSumRow && needSumRow) {
                if (!needSerialNumber) {
                    sumRow.put(headKey[0], sumRowName);
                }
                sheetData.add(sumRow);
                fillSumRow = true;
            }
            dataIterator++;
        }
    }

    /**
     * 生成图像插入excel
     *
     * @param wb
     * @param wbSheet
     */
    protected void createImg(XSSFWorkbook wb, XSSFSheet wbSheet) {
        try {
            //图像编码不为空
            if (Objects.nonNull(imgUrls) && !imgUrls.isEmpty()) {

                for (int i = 0; i < imgUrls.size(); i++) {
                    String imgBase64Info = imgUrls.get(i);
                    if (!StringUtils.isEmpty(imgBase64Info)) {
                        String[] imgUrlArr = imgBase64Info.split("base64,");
                        byte[] buffer = new BASE64Decoder().decodeBuffer(imgUrlArr[1].replaceAll(" ", "+"));
                        //生成图片保存路径
//                        String picPath = "C:\Users\jz\Desktop\echarts" + ".png";
//                        File file = new File(picPath);//图片文件
                        //生成图片
//                        OutputStream out = new FileOutputStream(file);//图片输出流
//                        out.write(buffer);
//                        out.flush();//清空流
//                        out.close();//关闭流
                        ByteArrayInputStream inputStream = new ByteArrayInputStream(buffer);
                        ByteArrayOutputStream outStream = new ByteArrayOutputStream(); // 将图片写入流中
                        BufferedImage bufferImg = ImageIO.read(inputStream);
                        ImageIO.write(bufferImg, "PNG", outStream);

                        // 利用XSSFPatriarch将图片写入EXCEL
                        XSSFDrawing patriarch = wbSheet.createDrawingPatriarch();
                        //short 宽度：0-12列，从第0列开始，到第12列结束
                        //row1-row2:高度：8-31列，从第12行开始，到第35行结束
                        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, imgLocation.get("startColumn").shortValue(), imgLocation.get("startRow"), imgLocation.get("startColumn") + imgLocation.get("columnWidth"), imgLocation.get("startRow") + imgLocation.get("rowHeight"));
                        //画图
                        patriarch.createPicture(anchor, wb.addPicture(outStream.toByteArray(), HSSFWorkbook.PICTURE_TYPE_PNG));
//                        if (file.exists()) {
//                            file.delete();//删除图片
//                        }

                        //关闭流
                        inputStream.close();
                        outStream.close();
                    }
                    //更新下一张表的起始位置
                    imgLocation.put("startRow", imgLocation.get("startRow") + imgLocation.get("rowHeight") + 2);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    protected void createMargeDateRow(XSSFSheet wbSheet, XSSFCellStyle dataCellStyle, List<Map<String, Object>> sheetData, int headerIndex, int headerRow) {
        // 行迭代
        int dataSatrtIndex = headerIndex + headerRow;
        Map<Integer, MergeModel> poiModels = new HashMap<Integer, MergeModel>();
        //循环写入表中数据
        int i = 0;
        for (; i < sheetData.size(); i++) {
            //数据行
            XSSFRow row = wbSheet.createRow((short) (i + dataSatrtIndex));
            //行内循环，既单元格（列）
            Map<String, Object> rowDate = sheetData.get(i);
            DecimalFormat decimalFormat = new DecimalFormat("0.00");
            int j = 0;
            for (String key : headKey) {
                Object o = rowDate.get(key);
                //数据列
                String content = "";
                if (o != null) {
                    if (o.toString().contains(".") && isNumeric(o.toString())) {
                        content = decimalFormat.format(Float.valueOf(o.toString()));
                    } else if (o.toString().contains("-") && o.toString().contains(":")) {
                        content = String.valueOf(o).split("\\.")[0];
                    } else {
                        content = String.valueOf(o);
                    }
                }

                if (isMerge && mergeIndex.contains(j)) {
                    //如果该列需要合并
                    MergeModel poiModel = poiModels.get(j);
                    if (poiModel == null) {
                        poiModel = new MergeModel();
                        poiModel.setContent(content);
                        poiModel.setRowIndex(i + dataSatrtIndex);
                        poiModel.setCellIndex(j);
                        poiModels.put(j, poiModel);
                    } else {
                        if (!poiModel.getContent().equals(content)) {
                            //如果不同了，则将前面的数据合并写入
                            if (isTree) {
                                //此列向后的所有列都进行一次写入合并操作，并清空。
                                //树结构中存在这种情况，a目录和b目录为同级目录，a目录下最后一个子目录和b目录下的第一个子目录名称相同，防止本来不应该合并的单元格被合并
                                addMergedRegionValue(wbSheet, dataCellStyle, poiModels, mergeIndex, i + dataSatrtIndex, poiModel.getCellIndex());
                            } else {
                                XSSFRow lastRow = wbSheet.getRow(poiModel.getRowIndex());
                                XSSFCell lastCell = lastRow.createCell(poiModel.getCellIndex());//创建列
                                lastCell.setCellValue(poiModel.getContent());
                                lastCell.setCellStyle(dataCellStyle);
                                //合并单元格
                                if (poiModel.getRowIndex() != i + dataSatrtIndex - 1) {
                                    wbSheet.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), i + dataSatrtIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));
                                }
                            }
                            //将新数据存入
                            poiModel.setContent(content);
                            poiModel.setRowIndex(i + dataSatrtIndex);
                            poiModel.setCellIndex(j);
                            poiModels.put(j, poiModel);
                        }
                    }
                    row.createCell(j);//创建单元格
                } else {//该列不需要合并
                    //数据列
                    XSSFCell cell = row.createCell(j);
                    cell.setCellValue(content);
                    cell.setCellStyle(dataCellStyle);
                }
                j++;
            }
        }
        //将最后一份存入
        if (poiModels != null && poiModels.size() != 0) {
            for (Integer key : poiModels.keySet()) {
                MergeModel poiModel = poiModels.get(key);
                XSSFRow lastRow = wbSheet.getRow(poiModel.getRowIndex());
                XSSFCell lastCell = lastRow.getCell(poiModel.getCellIndex());
                lastCell.setCellValue(poiModel.getContent());
                lastCell.setCellStyle(dataCellStyle);
                //合并单元格
                if (poiModel.getRowIndex() != i + dataSatrtIndex - 1) {
                    wbSheet.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), i + dataSatrtIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));
                }
            }
        }
    }

    private static void addMergedRegionValue(XSSFSheet sheet, XSSFCellStyle dataCellStyle, Map<Integer, MergeModel> poiModels, List<Integer> mergeIndex, int nowRowIndex, int nowCellIndex) {
        if (poiModels != null && poiModels.size() != 0 && mergeIndex != null && mergeIndex.size() != 0) {
            for (Integer index : mergeIndex) {
                if (index >= nowCellIndex) {
                    MergeModel poiModel = poiModels.remove(index);//删除并获取value
                    if (poiModel != null) {
                        XSSFRow lastRow = sheet.getRow(poiModel.getRowIndex());
                        XSSFCell lastCell = lastRow.createCell(poiModel.getCellIndex());//创建列
                        lastCell.setCellValue(poiModel.getContent());
                        lastCell.setCellStyle(dataCellStyle);
                        //合并单元格
                        if (poiModel.getRowIndex() != nowRowIndex - 1) {
                            sheet.addMergedRegion(new CellRangeAddress(poiModel.getRowIndex(), nowRowIndex - 1, poiModel.getCellIndex(), poiModel.getCellIndex()));
                        }
                    }
                }
            }
        }
    }


    /**
     * 判断是不是数字
     *
     * @param str
     * @return
     */
    private static boolean isNumeric(String str) {
        if (str == null || str.length() == 0) {
            return false;
        }
        Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
        return pattern.matcher(str).matches();
    }

    /**
     * 计算合计行
     *
     * @param data
     * @return
     * @throws IOException
     */
    public Map<String, Object> getSumRow(List<Map<String, Object>> data) throws IOException {
        // 校验
        checkConfig();
        // 合计行
        Map<String, Object> sumData = new HashMap<>();
        for (Map<String, Object> row : data) {
            for (Map.Entry<String, ExportFieldProperties> fieldType : fieldTypes.entrySet()) {
                ExportFieldProperties properties = fieldType.getValue();
                String type = properties.getType();
                String key = fieldType.getKey();
                // 只合计number类型的数据
                if (ExcelFieldType.NUMBER.equals(type) && (Objects.isNull(ignoreSumFields) || !Arrays.asList(ignoreSumFields).contains(key))) {
                    Object valueObj = row.get(key);
                    String valueStr = !isEmpty(valueObj) ? valueObj.toString().trim() : "0";
                    BigDecimal nowValue = new BigDecimal(valueStr).setScale(properties.getPoint(), RoundingMode.HALF_UP);
                    Object sumValue = sumData.get(key);
                    sumData.put(key, Objects.isNull(sumValue) ? nowValue : ((BigDecimal) sumValue).add(nowValue));
                }
            }
        }
        // 给第一列设置合计标题
        sumData.put(headKey[0], sumRowName);
        return sumData;
    }

    /**
     * List拷贝
     *
     * @param src
     * @param start
     * @param end
     * @return
     */
    private List<Map<String, Object>> copyList(List<Map<String, Object>> src, int start, int end) {
        List<Map<String, Object>> des = new ArrayList<>();
        for (int i = start; i <= end; i++) {
            des.add(src.get(i));
        }
        return des;
    }

    /**
     * 保留小数点格式生成
     *
     * @param point
     * @return
     */
    protected String generateDateFormat(int point) {
        StringBuilder sb = new StringBuilder();
        sb.append(point == 0 ? "0" : "0.");
        for (int i = 0; i < point; i++) {
            sb.append("0");
        }
        return sb.toString();
    }

    /**
     * 构建表头
     *
     * @param wbSheet
     * @param cellStyle
     * @param index
     */
    protected void createHeader(XSSFSheet wbSheet, XSSFCellStyle cellStyle, String[][] hfDataList, int index) {
        //合并数
        int mergerNum = 0;
        //给单元格设置值
        for (int i = 0; i < hfDataList.length; i++) {
            int rowIndex = i + index;
            XSSFRow mergeRow = wbSheet.createRow(rowIndex);
            for (int j = 0; j < hfDataList[i].length; j++) {
                XSSFCell cell = mergeRow.createCell(j);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(hfDataList[i][j]);
            }
        }
        Map<Integer, List<Integer>> mergeMap = new HashMap<>();   // 合并行时要跳过的行列
        //合并列
        for (int i = 0; i < hfDataList[hfDataList.length - 1].length; i++) {
            if ("".equals(hfDataList[hfDataList.length - 1][i])) {
                for (int j = hfDataList.length - 2; j >= 0; j--) {
                    int rowIndex = j + index;
                    if (!"".equals(hfDataList[j][i])) {
                        wbSheet.addMergedRegion(new CellRangeAddress(rowIndex, hfDataList.length - 1 + index, i, i)); // 合并单元格
                        break;
                    } else {
                        if (mergeMap.containsKey(j)) {
                            List<Integer> list = mergeMap.get(j);
                            list.add(i);
                            mergeMap.put(j, list);
                        } else {
                            List<Integer> list = new ArrayList<>();
                            list.add(i);
                            mergeMap.put(j, list);
                        }
                    }
                }
            }
        }
        //合并行
        for (int i = 0; i < hfDataList.length - 1; i++) {
            int rowIndex = i + index;
            for (int j = 0; j < hfDataList[i].length; j++) {
                List<Integer> list = mergeMap.get(i);
                if (list == null || !list.contains(j)) {
                    if ("".equals(hfDataList[i][j])) {
                        mergerNum++;
                        if (mergerNum != 0 && j == (hfDataList[i].length - 1)) {
                            wbSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, j - mergerNum, j)); // 合并单元格
                            mergerNum = 0;
                        }
                    } else {
                        if (mergerNum != 0) {
                            wbSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, j - mergerNum - 1, j - 1)); // 合并单元格
                            mergerNum = 0;
                        }
                    }
                }
            }
        }
    }

    /**
     * 构建标题
     *
     * @param wbSheet
     * @param titleCellStyle
     */
    protected void createTitle(XSSFSheet wbSheet, XSSFCellStyle titleCellStyle) {
        //在第0行创建表标题
        XSSFRow titleRow = wbSheet.createRow(0);
        titleRow.setHeightInPoints(30); //行高
        XSSFCell cellValue = titleRow.createCell(0);
        cellValue.setCellValue(title);
        cellValue.setCellStyle(titleCellStyle);
        int lastIndex = headList[0].length - 1;
        for (int i = 1; i <= lastIndex; i++) {
            // 创建被合并的单元格
            titleRow.createCell(i).setCellStyle(titleCellStyle);
        }
        // 合并标题行单元格
        wbSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, lastIndex));
    }

    /**
     * 构建单元格样式
     *
     * @param style
     * @return
     */
    protected XSSFCellStyle buildCellStyle(XSSFWorkbook wb, ExcelCellStyle style) {
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(style.getAlignment());//水平居中
        cellStyle.setVerticalAlignment(style.getVerticalAlignment());//垂直居中
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 背景颜色
        cellStyle.setFillForegroundColor((short) style.getBackgroundColor()); // 背景颜色
        cellStyle.setBorderTop(BorderStyle.THIN);  // 设置边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        XSSFFont fontStyle = wb.createFont();
        fontStyle.setColor((short) style.getColor());
        fontStyle.setFontName(style.getFontName());
        fontStyle.setBold(style.isBold());
        fontStyle.setFontHeightInPoints((short) style.getFontSize());  //设置标题字体大小
        cellStyle.setFont(fontStyle);
        return cellStyle;
    }

    /**
     * 检查数据配置问题
     *
     * @throws IOException 抛出数据异常类
     */
    protected void checkConfig() throws IOException {
        if (StringUtils.isBlank(sheetName)) {
            throw new IOException("列名数组不能为空");
        }
        if (headList == null || headList.length == 0) {
            throw new IOException("列名数组不能为空或者为NULL");
        }
        if (headKey == null || headKey.length == 0) {
            throw new IOException("字段数组不能为空或者为NULL");
        }
        if (headList[0].length != headKey.length) {
            throw new IOException("列名数组与字段数组长度不匹配");
        }
        if (fieldTypes == null) {
            throw new IOException("字段属性不能为NULL");
        }
        if (columnWidth < 0) {
            throw new IOException("字体、宽度或者高度不能为负值");
        }
    }

    /**
     * 给表头和key数组填充数据
     */
    protected void fillHeaderAndKey() {
        if (needSerialNumber) {
            // 拷贝表头数组
            for (int i = 0; i < headList.length; i++) {
                String[] head = headList[i];
                String[] newHeader = new String[head.length + 1];
                System.arraycopy(head, 0, newHeader, 1, head.length);
                if (i == 0) {
                    newHeader[0] = "序号";
                } else {
                    newHeader[0] = "";
                }
                headList[i] = newHeader;
            }
            // 拷贝key数组
            String[] newHeadKey = new String[headKey.length + 1];
            System.arraycopy(headKey, 0, newHeadKey, 1, headKey.length);
            newHeadKey[0] = "index";
            headKey = newHeadKey;
        }
    }

    /**
     * 填充指定格式
     *
     * @param fields
     * @return
     */
    public Map<String, ExportFieldProperties> fillFieldTypes(String[] fields) {
        Map<String, ExportFieldProperties> fieldTypes = new HashMap<>();
        for (String field : fields) {
            ExportFieldProperties fieldProperties = new ExportFieldProperties();
            fieldProperties.setType(ExcelFieldType.NUMBER);
            String[] fieldArr = field.split("::");
            String fieldName = field;
            if (fieldArr.length == 2) {
                fieldName = fieldArr[0];
                fieldProperties.setPoint(Integer.parseInt(fieldArr[1]));
            } else {
                fieldProperties.setPoint(2);
            }
            fieldTypes.put(fieldName, fieldProperties);
        }
        return fieldTypes;
    }


    /**
     * 判断对象是否为空
     *
     * @param obj
     * @return
     */
    public boolean isEmpty(Object obj) {
        if (Objects.isNull(obj)) {
            return true;
        } else if (obj instanceof String) {
            String objStr = (String) obj;
            if (StringUtils.isBlank(objStr)) {
                return true;
            }
            return "null".equalsIgnoreCase(objStr.trim());
        }
        return false;
    }

    /**
     * 将JSON文件解析成Map对象
     *
     * @param jsonFile
     * @return
     */
    public Map<String, ExportFieldProperties> resolveJsonToMap(File jsonFile) {
        String jsonStr = "";
        try {
            FileReader fileReader = new FileReader(jsonFile);
            Reader reader = new InputStreamReader(new FileInputStream(jsonFile), StandardCharsets.UTF_8);
            int ch = 0;
            StringBuilder sb = new StringBuilder();
            while ((ch = reader.read()) != -1) {
                sb.append((char) ch);
            }
            fileReader.close();
            reader.close();
            jsonStr = sb.toString();
            return JSON.parseObject(jsonStr, new TypeReference<Map<String, ExportFieldProperties>>() {
            });
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }
}

